import datetime
import time
import traceback

import pymysql

from back.models import User, Recarticles
from front.DBhelp import DBUtui

from front.models import Article


class UserService:
    @staticmethod
    def findByname(uname):
        #根据用户名查找
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            sql="select uid,username,password,selfslogo,lastlogintime,selfphoto,aboutme from user where username='{0}';".format(uname)
            cursor.execute(sql)
            data=cursor.fetchone()
            if data:
                #封装到实体类中
                u=User(data['uid'],data['selfslogo'],data['selfphoto'],data['aboutme'])
                u.uname=data['username']
                u.pwd=data['password']
                u.lastlogintime=data['lastlogintime']
                return u
            else:
                return
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def updateTime(uname):
        #更新登录时间
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            newsj=datetime.datetime.now()
            sql="update user set LastLoginTime='{0}' where username='{1}'".format(newsj,uname)
            cursor.execute(sql)
            db.commit()
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def getUsermima(uname):
        #根据用户名查找密码
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            sql="select password from user where username='{0}'".format(uname)
            cursor.execute(sql)
            data=cursor.fetchone()
            if data:
                u=User('','','','')
                u.pwd=data['password']
                return u
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def xiugaiPwd(uname,newpwd):
        #修改密码
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            newsj=datetime.datetime.now()
            sql="update user set password='{0}' where username='{1}'".format(newpwd,uname)
            data=cursor.execute(sql)
            if data:
                db.commit()
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def updateInfo(id,selfSlogo,picUrl,aboutMe):
        sql="update user set selfslogo='{0}',selfphoto='{1}',aboutme='{2}' where uid={3}".format(selfSlogo,picUrl,aboutMe,id)
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            data=cursor.execute(sql)
            if data>0:
                db.commit()
                return True
            else:
                return False
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
class ArticleService:
    @staticmethod
    def getAlist(title,type,ispub):
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            sql="select id,title,type,tags,ispub,createtime,pubtime from articles where showtype='文章'"
            if title !=None and title !='':
                sql += " and title like '%{0}%' ".format(title)
            if type!=None and type!='':
                sql += " and type='{0}' ".format(type)
            if  ispub!=None and ispub!='':
                sql += " and ispub='{0}' ".format(ispub)
            sql+=" order by createtime desc"
            cursor.execute(sql)
            data=cursor.fetchall()
            if data:
                list = []
                #封装到实体类
                for d in data:
                    aobj=Article(d['id'],d['title'],'','')
                    aobj.type = d['type']
                    aobj.tags = d['tags']
                    aobj.ispub = d['ispub']
                    aobj.createtime = d['createtime']
                    aobj.pubtime = d['pubtime']
                    list.append(aobj)
                return list
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def delete(id):
        #行内工具栏删除
        try:
            print(id)
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            sql="delete from articles where id={0}".format(id)
            data=cursor.execute(sql)
            if data>0:
                db.commit()
                return True
            else:
                return False
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def getTags():
        sql="select distinct tags from articles where showtype='文章'"
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data=cursor.fetchall()
            return data
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)

    @staticmethod
    def getTagses():
        sql = "select distinct tags from articles where showtype='相册'"
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data = cursor.fetchall()
            return data
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def getType():
        #文章
        sql="select distinct type from articles where showtype='文章'"
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data=cursor.fetchall()
            return data
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)

    @staticmethod
    def getTypese():
        #相册
        sql = "select distinct type from articles where showtype='相册'"
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data = cursor.fetchall()
            return data
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def save(title,tags,simpleinfo,cont,PicUrl,source,type,ispub):
        #添加
        newtime=datetime.datetime.now()
        if ispub=='是':
            sql="insert into articles(title,tags,simpleinfo,cont,mainpicurl,source,type,ispub,showtype,createtime,pubtime) " \
                "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','文章','{8}','{9}')".format(title,tags,simpleinfo,cont,PicUrl,source,type,ispub,newtime,newtime)
        else:
            sql = "insert into articles(title,tags,simpleinfo,cont,mainpicurl,source,type,ispub,showtype,createtime) " \
                  "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','文章','{8}')".format(title,tags,simpleinfo,cont,PicUrl,source,type,ispub,newtime)
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            data=cursor.execute(sql)
            if data>0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def batchDel(ids):
        #批量删除
        sql="delete from articles where id in ({0})".format(ids)
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            data=cursor.execute(sql)
            if data>0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def batchpub(ids):
        #批量发布
        newtime=time.strftime('%Y-%m-%d %H-%M-%S',time.localtime())
        ntime=datetime.datetime.now()
        sql = "update articles set ispub='{0}',pubtime='{1}' where id in ('{2}')".format('是',ntime,ids)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def findByid(aid):
        #查找id
        sql="select id,title,tags,simpleinfo,cont,mainpicurl,source,type from articles where showtype='文章' and id={0}".format(aid)
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data=cursor.fetchone()
            if data:
                aobj=Article(data['id'],data['title'],data['simpleinfo'],data['cont'])
                aobj.tags=data['tags']
                aobj.picurl = data['mainpicurl']
                aobj.source = data['source']
                aobj.type = data['type']
                aobj.showtype='文章'
                return aobj
            else:
                return None
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)
    @staticmethod
    def edit(id,title,tags,simpleinfo,cont,picurl,source,type):
        sql = "update articles set title='{0}', tags='{1}', simpleinfo = '{2}', " \
              "cont = '{3}',mainpicurl = '{4}',source ='{5}',type = '{6}' where id = {7}".\
              format(title,tags,simpleinfo, cont, picurl, source, type, id)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
            else:
                return
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
class RecommentService:
    @staticmethod
    def getrecommtnts(posid,minweight,maxweight):
        sql="select r.id rid,title,showtype,posid,weight from articles a,recarticles r where r.articleid=a.id"
        if posid!=None and posid!='':
            sql+=" and posid={0}".format(posid)
        if minweight!= None and minweight != '':
            sql+=" and weight>={0}".format(minweight)
        if  maxweight!= None and maxweight != '':
            sql+=" and weight<={0}".format(maxweight)
        #print(sql)
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            data=cursor.fetchall()
            if data:
                list=[]
                for temp in data:
                    aobj=Article('',temp['title'],'','')
                    aobj.showtype=temp['showtype']
                    robj=Recarticles(temp['rid'],aobj,temp['posid'],temp['weight'])
                    list.append(robj)
                return list
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor,db)

    @staticmethod
    def findByAidAndPid(aid, posid):
        #根据aid 和 posid 查找 id  是否为空   表示同一文章在相同位置不能重复推荐
        sql = "select id from recarticles where articleid={0} and posid={1}".format(aid, posid)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            res = cursor.fetchone()
            if res:
                return False
            else:
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def save(aid,posid,weight):
        #文章管理中   推荐文章
        sql="insert into recarticles(articleid,posid,weight) values({0},{1},{2})".format(aid,posid,weight)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
        return False

    @staticmethod
    def recommentDel(ids):
        #推荐列表   批量删除
        sql = "delete from recarticles where id in ({0})".format(ids)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def singleDel(id):
        # 推荐列表   单条删除
        sql = "delete from recarticles where id={0}".format(id)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def findById(id):
        sql = "select r.id rid,title,posid,weight,showtype from recarticles r,articles a where r.articleid=a.id and r.id={0}".format(id)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            res = cursor.fetchone()
            if res:
                aobj = Article('', res['title'], '', '')
                aobj.showtype = res['showtype']
                robj = Recarticles(res['rid'], aobj, res['posid'], res['weight'])
                return robj
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def findAllpostion():
        sql="select id,position_desc from postiondesc "
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute(sql)
            res = cursor.fetchall()
            if res:
                return res
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def edit(id,weight,posid):
        sql="update recarticles set posid={0},weight={1} where id={2} ".format(posid,weight,id)
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            data = cursor.execute(sql)
            if data > 0:
                db.commit()
                return True
        except:
            traceback.print_exc()
        finally:
            DBUtui.close(cursor, db)
    @staticmethod
    def getgallery(txtTitle, type, ispub):
        try:
            coon = DBUtui.getconnect()
            cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "SELECT id,title,type,tags,ispub,createtime,mainpicurl,pubtime FROM articles where ShowType='相册'"
            if txtTitle != '' and txtTitle != None:
                sql += "and title like '%{0}%'".format(txtTitle)
            if type != '' and type != None:
                sql += "and type='{0}'".format(type)
            if ispub != '' and ispub != None:
                sql += "and ispub='{0}'".format(ispub)
            sql += "ORDER BY CreateTime DESC"
            cur.execute(sql)
            date = cur.fetchall()
            list = []
            ##id,title,simpleinfo,cont
            for t in date:
                aobj = Article(t['id'], t['title'], '', '')
                ##Type,Tags,IsPub,CreateTime,ShowType
                aobj.type = t['type']
                aobj.tags = t['tags']
                aobj.ispub = t['ispub']
                aobj.createtime = t['createtime']
                aobj.mainpicurl = t['mainpicurl']
                aobj.pubtime = t['pubtime']
                list.append(aobj)
            return list
        except BaseException as e:
            print('程序出错：', e)
        finally:
            DBUtui.close(cur, coon)
    @staticmethod
    def gallerysave(title, tags, SimpleInfo, Cont, Source, type, PicUrl, IsPub, time):
        try:
            coon = DBUtui.getconnect()
            cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
            if IsPub == '否':
                sql = "INSERT INTO articles(title,tags,SimpleInfo,Cont,Source,type,MainPicUrl,IsPub,ShowType,createtime) " \
                      "VALUES ( '{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','相册','{8}')".format(title, tags, SimpleInfo, Cont, Source, type, PicUrl, IsPub,time)
            elif IsPub == '是':
                sql = "INSERT INTO articles(title,tags,SimpleInfo,Cont,Source,type,MainPicUrl,IsPub,ShowType,createtime,PubTime) " \
                      "VALUES ( '{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','相册','{8}','{9}')".format(title, tags, SimpleInfo,Cont,Source,type,PicUrl,IsPub, time,datetime.datetime.now())
            row = cur.execute(sql)
            if row > 0:
                coon.commit()
                # print ( '添加成功' )
                return True
        except BaseException as e:
            print('程序出错:', e)
        finally:
            DBUtui.close(cur, coon)
    # 相册的标签
    @staticmethod
    def gallerytags():
        try:
            coon = DBUtui.getconnect()
            cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "SELECT distinct tags from articles WHERE ShowType='相册'"
            cur.execute(sql)
            date = cur.fetchall()
            return date
        except BaseException as e:
            print('程序出错：', e)
        finally:
            DBUtui.close(cur, coon)
    # 查询文章分类
    @staticmethod
    def galleryType():
        try:
            coon = DBUtui.getconnect()
            cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "SELECT distinct type from articles WHERE ShowType='相册'"
            cur.execute(sql)
            date = cur.fetchall()
            return date
        except BaseException as e:
            print('程序出错：', e)
        finally:
            DBUtui.close(cur, coon)
    @staticmethod
    def findgalleryById(aid):
        try:
            coon = DBUtui.getconnect()
            cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "select id,Title,tags,SimpleInfo,cont,mainpicurl,source,type,showtype from articles where ShowType='相册' and id={0}".format(aid)
            cur.execute(sql)
            date = cur.fetchone()
            aobj = Article(date['id'], date['Title'], date['SimpleInfo'], date['cont'])
            aobj.tags = date['tags']
            aobj.picurl = date['mainpicurl']
            aobj.source = date['source']
            aobj.type = date['type']
            aobj.showtype = date['showtype']
            return aobj
        except BaseException as e:
            print('程序出错：', e)
        finally:
            DBUtui.close(cur, coon)
